using System;
using System.Text;
using System.Collections.Generic;

namespace UnityORM
{
	public class SQLMaker
	{
		public readonly static DateTime UnixTime = new DateTime (1970, 1, 1);
		
		enum SQLTYPE
		{
			INTEGER,
			
			FLOAT,
			
			BLOB,
		};
		
		private Dictionary<Type, SQLTYPE> mappingSQLType = new Dictionary<Type, SQLTYPE> ();
		private Dictionary<Type, int> mappingSQLLength = new Dictionary<Type, int> ();
		
		public SQLMaker ()
		{
			mappingSQLType.Add (typeof(int), SQLTYPE.INTEGER);
			mappingSQLType.Add (typeof(int?), SQLTYPE.INTEGER);
			//mappingSQLLength.Add(typeof(int), 11);
			//mappingSQLLength.Add(typeof(int?), 11);
			mappingSQLType.Add (typeof(long), SQLTYPE.INTEGER);
			mappingSQLType.Add (typeof(long?), SQLTYPE.INTEGER);
			mappingSQLType.Add (typeof(bool), SQLTYPE.INTEGER);
			mappingSQLType.Add (typeof(bool?), SQLTYPE.INTEGER);
			mappingSQLLength.Add (typeof(bool), 1);
			mappingSQLLength.Add (typeof(bool?), 1);
			mappingSQLType.Add (typeof(byte), SQLTYPE.INTEGER);
			mappingSQLType.Add (typeof(byte?), SQLTYPE.INTEGER);
			mappingSQLType.Add (typeof(char), SQLTYPE.INTEGER);
			mappingSQLType.Add (typeof(char?), SQLTYPE.INTEGER);
			mappingSQLType.Add (typeof(float), SQLTYPE.FLOAT);
			mappingSQLType.Add (typeof(float?), SQLTYPE.FLOAT);
			mappingSQLType.Add (typeof(double), SQLTYPE.FLOAT);
			mappingSQLType.Add (typeof(double?), SQLTYPE.FLOAT);
			mappingSQLType.Add (typeof(DateTime), SQLTYPE.INTEGER);
		}
		
		public string GenerateCreateTableSQL<T> (ClassDesc desc)
		{
			var builder = new StringBuilder ();	
			builder.Append ("CREATE TABLE IF NOT EXISTS " + desc.Name + "(");
			foreach (FieldDesc fieldDesc in desc.FieldDescs) {
				builder.Append (fieldDesc.NameInTable + " " + ConvertToSQLDefine (fieldDesc));
				if (fieldDesc == desc.KeyField) {
					builder.Append (" PRIMARY KEY");
				}
				
				if (desc.AutoIncrement) {
					builder.Append (" AUTOINCREMENT");
				}
				
				builder.Append (",");
			}
			
			builder.Remove (builder.Length - 1, 1);
			builder.Append (");");
			return builder.ToString ();
		}
		
		string ConvertToSQLDefine (FieldDesc fieldDesc)
		{
			SQLTYPE sqlType;
			if (mappingSQLType.TryGetValue (fieldDesc.FieldType, out sqlType)) {
				switch (sqlType) {
				case SQLTYPE.INTEGER:
					return "INTEGER";
					
				case SQLTYPE.FLOAT:
					return "FLOAT";
					
				case SQLTYPE.BLOB:
					return "BLOB";
				}
			}
			
			return "TEXT";
		}
		
		public string GenerateSelectAllSQL<T> (ClassDesc desc)
		{
			return "SELECT * FROM " + desc.Name + ";";
		}
		
		public string GenerateSelectSQL<T> (ClassDesc desc, object key)
		{
			if (desc.KeyField == null)
				throw new Exception ("Class " + desc.Name + " hasn't key field");
			return "SELECT * FROM " + desc.Name + " WHERE " + desc.KeyField.NameInTable + " = " + ValueToBlock (key) + ";";
		}
		
		public string GenerateDeleteAllSQL<T> (ClassDesc desc)
		{
			return "DELETE FROM " + desc.Name + ";";
		}
		
		public string GenerateDeleteSQL<T> (ClassDesc desc, object key)
		{
			if (desc.KeyField == null)
				throw new Exception ("Class " + desc.Name + " hasn't key field");
			return "DELETE FROM " + desc.Name + " WHERE " + desc.KeyField.NameInTable + " = " + ValueToBlock (key) + ";";
		}
		
		public string GenerateInsertSQL<T> (ClassDesc desc, T obj)
		{
			var builder = new StringBuilder ();	
			builder.Append ("INSERT INTO " + desc.Name + " (");
			
			var fields = desc.FieldDescs;
			if (desc.AutoIncrement) {
				fields = new List<UnityORM.FieldDesc> (fields);
				fields.Remove (desc.KeyField);
			}
			
			foreach (var f in fields) {
				builder.Append (f.NameInTable + ",");
			}
			
			builder.Remove (builder.Length - 1, 1);
			builder.Append (") VALUES (");
			
			foreach (var f in fields) {
				object v = f.GetForDb (obj);
				builder.Append (ValueToBlock (v) + ",");
			}
			
			builder.Remove (builder.Length - 1, 1);
			builder.Append (");");
			
			return builder.ToString ();
		}
		
		public string GenerateUpdateSQL<T> (ClassDesc desc, T obj)
		{
			if (desc.KeyField == null)
				throw new Exception ("Class " + desc.Name + " hasn't key field");
			var builder = new StringBuilder ();	
			builder.Append ("UPDATE " + desc.Name + " SET ");
			foreach (var f in desc.FieldDescs) {
				object v = f.GetForDb (obj);
				builder.Append (f.NameInTable + "=" + ValueToBlock (v) + ",");
			}
			
			builder.Remove (builder.Length - 1, 1);
			builder.Append (" WHERE " + desc.KeyField.NameInTable + " = " + ValueToBlock (desc.KeyField.GetForDb (obj)) + ";");
			
			return builder.ToString ();
		}
		
		string ValueToBlock (object v)
		{
			if (v == null) {
				return "NULL";
			} else {
				return "'" + Escape (v.ToString ()) + "'";
			}
		}
		
		public string Escape (string str)
		{
			return str.Replace ("'", "''");
		}
	}
}

